/*
 * @Author: 白川 杏 baiyufeisteam@163.com
 * @Date: 2023-05-15 17:38:31
 * @LastEditors: 白川 杏 baiyufeisteam@163.com
 * @LastEditTime: 2023-06-11 10:18:07
 * @FilePath: \junior-project-training\backend\koaserver\routes\news.js
 * @Description: 这是默认设置,请设置`customMade`, 打开koroFileHeader查看配置 进行设置: https://github.com/OBKoro1/koro1FileHeader/wiki/%E9%85%8D%E7%BD%AE
 */
//News
const router = require('koa-router')()
const fs = require("fs");
const path = require("path");
const uploadDir = path.join(__dirname, '../images/newsimgs');

router.prefix('/news')

//newsimg
//页面初始化时获取数据库内的数据条目
router.post('/getnewsmgbackend', async (ctx, next) => {
  let sql = 'SELECT * FROM newsimg ORDER BY newsimgid'
  let result = await ctx.db.EXCUTE(sql);
  ctx.body = {
    code: 200,
    data: result
  }
})

//尝试合并
//页面初始化时获取数据库内的数据条目
router.post('/getnewsbackend', async (ctx, next) => {
  await ctx.db.EXCUTE("SET @@group_concat_max_len = 102400000000")
  let resultuserid = await ctx.db.EXCUTE("SELECT (SELECT username FROM user ni WHERE n.userid=ni.userid) AS userinfo FROM news n")
  let resultcropsid = await ctx.db.EXCUTE("SELECT (SELECT cropname FROM tags ni WHERE n.cropsid=ni.cropsid) AS cropinfo FROM news n")
  let result1 = await ctx.db.EXCUTE("SELECT n.*, (SELECT GROUP_CONCAT(newsimgs SEPARATOR '><') FROM newsimg ni WHERE n.newsid=ni.newsid) AS comings FROM news n")

  for (let i = 0; i < result1.length; i++) {
    result1[i].comings = (result1[i].comings || '').split('><');
    Object.assign(result1[i], resultuserid[i]);
    Object.assign(result1[i], resultcropsid[i]);
  }

  ctx.body = {
    code: 200,
    data: result1
  }
})

//审核操作
router.post('/auditbackend', async (ctx, next) => {
  let newsid = ctx.request.body.newsid;
  let sqlaudit = 'UPDATE news SET isAudit = 1 WHERE newsid=?'
  await ctx.db.EXCUTE(sqlaudit, [newsid]);

  await ctx.db.EXCUTE("SET @@group_concat_max_len = 102400000000")
  let resultuserid = await ctx.db.EXCUTE("SELECT (SELECT username FROM user ni WHERE n.userid=ni.userid) AS userinfo FROM news n")
  let resultcropsid = await ctx.db.EXCUTE("SELECT (SELECT cropname FROM tags ni WHERE n.cropsid=ni.cropsid) AS cropinfo FROM news n")
  let result1 = await ctx.db.EXCUTE("SELECT n.*, (SELECT GROUP_CONCAT(newsimgs SEPARATOR '><') FROM newsimg ni WHERE n.newsid=ni.newsid) AS comings FROM news n")

  for (let i = 0; i < result1.length; i++) {
    result1[i].comings = (result1[i].comings || '').split('><');
    Object.assign(result1[i], resultuserid[i]);
    Object.assign(result1[i], resultcropsid[i]);
  }

  ctx.body = {
    code: 200,
    data: result1
  }
})

//审核不通过操作
router.post('/auditFailedbackend', async (ctx, next) => {
  let newsid = ctx.request.body.newsid;
  let newsimg = ctx.request.body.newsimg;

  let sqlaudit = 'DELETE FROM news WHERE newsid=?'
  await ctx.db.EXCUTE(sqlaudit, [newsid]);

  //在数据库中删除newsimg表对应的文件
  for (let i = 0; i < newsimg.length; i++) {
    let m = newsimg[i].replace('/http://localhost:8080/', '')
    let n = newsimg[i].split('http://localhost:8080/')[1]
    console.log(m);
    console.log(n);
    fs.unlink(path.join(__dirname, '../' + n), async function (err) {
      if (err) {
        console.log(err);
        ctx.body = {
          code: 400
        }
      }
      await ctx.db.EXCUTE(`DELETE FROM newsimg WHERE newsid=${newsid}`)
    })
  }

  await ctx.db.EXCUTE("SET @@group_concat_max_len = 102400000000")
  let resultuserid = await ctx.db.EXCUTE("SELECT (SELECT username FROM user ni WHERE n.userid=ni.userid) AS userinfo FROM news n")
  let resultcropsid = await ctx.db.EXCUTE("SELECT (SELECT cropname FROM tags ni WHERE n.cropsid=ni.cropsid) AS cropinfo FROM news n")
  let result1 = await ctx.db.EXCUTE("SELECT n.*, (SELECT GROUP_CONCAT(newsimgs SEPARATOR '><') FROM newsimg ni WHERE n.newsid=ni.newsid) AS comings FROM news n")

  for (let i = 0; i < result1.length; i++) {
    result1[i].comings = (result1[i].comings || '').split('><');
    Object.assign(result1[i], resultuserid[i]);
    Object.assign(result1[i], resultcropsid[i]);
  }

  ctx.body = {
    code: 200,
    data: result1
  }
})

//页面初始化时获取数据库内的数据条目
router.get('/getnews', async (ctx, next) => {
  let result = await ctx.db.EXCUTE('SELECT user.userid,user.avatar,user.nickname,news.newstext,news.addtime,news.newsid,tags.cropname FROM news,user,tags WHERE news.userid = user.userid and news.cropsid = tags.cropsid order by newsid desc');
  for (let i = 0; i < result.length; i++) {
    let r = await ctx.db.EXCUTE('SELECT * from newsimg where newsimg.newsid =' + result[i].newsid);
    if (r.length) {
      result[i].newsimg = r;
    }
  }
  ctx.body = result;
})



//删除news表item
router.post('/delnews', async (ctx, next) => {
  let id = ctx.request.body.newsid;

  //在数据库中删除对应数据
  await ctx.db.EXCUTE(`DELETE FROM news WHERE newsid=${id}`)
  //WHERE的筛选条件必须是主键

  //获取删除后的全部数据
  let result = await ctx.db.EXCUTE('SELECT * FROM news ORDER BY newsid');
  ctx.body = {
    code: 200,
    data: result
  }
})
//根据collect_id=>newsid获取news详情
router.post('/single', async (ctx, next) => {
  let newsid = ctx.request.body.newsid;
  let result = await ctx.db.EXCUTE('SELECT user.avatar,user.nickname,news.newstext,news.addtime,news.newsid,tags.cropname FROM news,user,tags WHERE news.userid = user.userid and news.cropsid = tags.cropsid and newsid=' + newsid);
  let r = await ctx.db.EXCUTE('SELECT * from newsimg where newsimg.newsid =' + newsid);
  result[0].newsimg = r;
  console.log(result);
  if (result[0]) {
    ctx.body = {
      code: 200,
      data: result,
    }
  } else {
    ctx.body = {
      code: 200,
      data: result,
    }
  }
})
router.post('/comment', async (ctx, next) => {
  let newsid = ctx.request.body.newsid;
  console.log(newsid);
  let result = await ctx.db.EXCUTE('SELECT user.avatar,user.nickname,user.userid,newscomment.newscom_id,newscomment.parent_comment_id,newscomment.content,newscomment.create_time FROM user,newscomment WHERE newscomment.userid = user.userid and newsid=' + newsid);
  console.log(result);
  if (result[0]) {
    ctx.body = {
      code: 200,
      data: result,
      msg: '存在newscomment'
    }
  } else {
    ctx.body = {
      code: 200,
      data: result,
      msg: '暂时还没有评论喔'
    }
  }
})
// 获取话题详情
router.post('/topic', async (ctx, next) => {
  let cropsid = ctx.request.body.cropsid;
  console.log(cropsid);
  let result = await ctx.db.EXCUTE('SELECT * FROM tags WHERE cropsid=' + cropsid);
  console.log(result);
  if (result[0]) {
    ctx.body = {
      code: 200,
      data: result,
    }
  } else {
    ctx.body = {
      code: 200,
      data: result,
    }
  }
})
// 获取话题相关动态
router.post('/topicnews', async (ctx, next) => {
  let cropsid = ctx.request.body.cropsid;
  console.log(cropsid);
  let result = await ctx.db.EXCUTE('SELECT user.userid,user.avatar,user.nickname,news.newstext,news.addtime,news.newsid FROM news,user WHERE news.userid = user.userid and news.cropsid=' + cropsid);
  console.log(result);
  for (let i = 0; i < result.length; i++) {
    let r = await ctx.db.EXCUTE('SELECT * from newsimg where newsimg.newsid =' + result[i].newsid);
    if (r.length) {
      result[i].newsimg = r;
    }
  }
  if (result[0]) {
    ctx.body = {
      code: 200,
      data: result,
    }
  } else {
    ctx.body = {
      code: 500,
      data: '暂时没有人参与讨论'
    }
  }
})
// 更多话题
router.get('/moretopics', async (ctx, next) => {
  let result = await ctx.db.EXCUTE('SELECT cropsid,cropname FROM tags');
  if (result[0]) {
    ctx.body = {
      code: 200,
      data: result
    }
  }
  else {
    ctx.body = {
      code: 500
    }
  }
})
// 收藏
router.post('/collect', async (ctx, next) => {
  let guideid = ctx.request.body.guideid;
  let collected = ctx.request.body.collected;
  let userid = ctx.request.body.userid;
  let collect_time = ctx.request.body.collect_time;
  console.log(guideid, collected, userid, collect_time);
  if (collected == true) {
    let sql = 'Insert into collect(userid,guideid,collect_time) values(?,?,?)';
    await ctx.db.EXCUTE(sql, [userid, guideid, collect_time]);
    ctx.body = {
      code: 200
    }
  } else {
    let sql = 'delete from collect where userid = ? and guideid = ? ';
    await ctx.db.EXCUTE(sql, [userid, guideid]);
    ctx.body = {
      code: 500
    }
  }
})
// 官方主帖子
router.post('/main', async (ctx, next) => {
  let newsType = ctx.request.body.newsType;
  if (newsType == '') {
    let result = await ctx.db.EXCUTE('SELECT * from guide ORDER BY guideid');
    ctx.body = {
      code: 200,
      data: result
    }
  }
  else {
    let sql = 'SELECT * from guide where classification = ? ORDER BY guideid';
    let result = await ctx.db.EXCUTE(sql, [newsType]);
    // console.log(result);
    // console.log(result.length);
    ctx.body = {
      code: 200,
      data: result
    }
  }
})

// 发布动态
router.post('/addnews', async (ctx, next) => {
  let newsid = ctx.request.body.newsid;
  let imgs = ctx.request.body.imgs;
  let userid = ctx.request.body.userid;
  let newstext = ctx.request.body.newstext;
  let addtime = ctx.request.body.addtime;
  let cropsid = ctx.request.body.cropsid;
  let isAudit = 0;
  console.log('内容', newstext);
  console.log('id', cropsid);
  let sql = 'insert into news (newsid, userid, newstext, addtime, cropsid, isAudit) values(?,?,?,?,?,?)';
  let result = await ctx.db.EXCUTE(sql, [newsid, userid, newstext, addtime, cropsid, isAudit]);
  if (imgs.length != 0) {
    for (let i in imgs) {
      // console.log(ctx.request.body.data.list.length);
      let base64 = imgs[i];
      let dataBuffer = Buffer.from(base64, 'base64');
      console.log(dataBuffer);
      let name = Date.now() + Math.floor(Math.random()) + '.jpg'
      let imgUrl = uploadDir + '\\' + name
      fs.writeFileSync(imgUrl, dataBuffer)
      let sql1 = "insert into newsimg (newsid, newsimgs) values (?, ?)"
      console.log(name);
      await ctx.db.EXCUTE(sql1, [newsid, '/images/newsimgs/' + name])
    }
  }
  ctx.body = {
    code: 200,
  }
})

router.post('/getmynews', async (ctx, next) => {
  let userid = ctx.request.body.userid;
  let sql = 'SELECT * from news,user,tags where news.userid=? and news.userid = user.userid and news.cropsid = tags.cropsid'
  let result = await ctx.db.EXCUTE(sql, [userid]);
  for (let i = 0; i < result.length; i++) {
    let r = await ctx.db.EXCUTE('SELECT * from newsimg where newsimg.newsid =' + result[i].newsid);
    if (r.length) {
      result[i].newsimg = r;
    }
  }
  console.log(result);
  ctx.body = {
    code: 200,
    data: result
  }
})

router.post('/deleteNews', async (ctx, next) => {
  let newsid = ctx.request.body.newsid;
  let sql = 'delete from news where newsid = ?';
  let result = await ctx.db.EXCUTE(sql, [newsid]);
  console.log(result);
  if (result.protocol41) {
    ctx.body = {
      code: 200,
    }
  }
})

module.exports = router
